﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using CardLogistics.Model.CardArrival;

namespace CardLogistics.DAL.CardArrival
{
    /// <summary>
    /// 卡片验收入库 数据访问类
    /// </summary>
    public class Storage_Check_CardData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_Storage_Check_Card", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Storage_Check_Card GetModel(string rId)
        {
            Storage_Check_Card model = new Storage_Check_Card();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["CHECKNO"] != null)
                {
                    model.CHECKNO = row["CHECKNO"].ToString();
                }
                if (row["STORAGETYPE"] != null)
                {
                    model.STORAGETYPE = row["STORAGETYPE"].ToString();
                }
                if (row["ORDERID"] != null)
                {
                    model.ORDERID = row["ORDERID"].ToString();
                }
                if (row["REMARK"] != null)
                {
                    model.REMARK = row["REMARK"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null)
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }

                if (row["EXAMINER"] != null)
                {
                    model.EXAMINER = row["EXAMINER"].ToString();
                }
                if (row["STORAGEDATE"] != null && row["STORAGEDATE"].ToString() != "")
                {
                    model.STORAGEDATE = DateTime.Parse(row["STORAGEDATE"].ToString());
                }
                if (row["SPAREPARTS"] != null && row["SPAREPARTS"].ToString() != "")
                {
                    model.SPAREPARTS = decimal.Parse(row["SPAREPARTS"].ToString());
                }
                if (row["PREMIUMS"] != null && row["PREMIUMS"].ToString() != "")
                {
                    model.PREMIUMS = decimal.Parse(row["PREMIUMS"].ToString());
                }
                if (row["IsSubmit"] != null && row["IsSubmit"].ToString() != "")
                {
                    model.IsSubmit = int.Parse(row["IsSubmit"].ToString());
                }
                if (row["DueNumber"] != null && row["DueNumber"].ToString() != "")
                {
                    model.DUENUMBER = int.Parse(row["DueNumber"].ToString());
                }

                #region 添加便于查询的字段
                if (row["OrderNo"] != null && row["OrderNo"].ToString() != "")
                {
                    model.OrderNo = row["OrderNo"].ToString();//订单编号
                }
                if (row["storageTypeName"] != null && row["storageTypeName"].ToString() != "")
                {
                    model.TypeName = row["storageTypeName"].ToString();//入库类型名称
                }
                #endregion
            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(Storage_Check_Card model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
					   new OracleParameter("vCHECKNO", OracleType.VarChar,50),
					new OracleParameter("vSTORAGETYPE", OracleType.VarChar,50),
					new OracleParameter("vORDERID", OracleType.VarChar,50),
					new OracleParameter("vREMARK", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("vEXAMINER", OracleType.VarChar,50),
                	new OracleParameter("vSTORAGEDATE", OracleType.DateTime),
                	new OracleParameter("vSPAREPARTS", OracleType.Number,4),
					new OracleParameter("vPREMIUMS", OracleType.Number,4),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("vDueNumber", OracleType.Number,4)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.CHECKNO;
                parameters[2].Value = model.STORAGETYPE;
                parameters[3].Value = model.ORDERID;
                parameters[4].Value = model.REMARK;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.EXAMINER;
                parameters[8].Value = model.STORAGEDATE;
                parameters[9].Value = model.SPAREPARTS;
                parameters[10].Value = model.PREMIUMS;
                parameters[11].Value = model.IsSubmit;
                parameters[12].Value = model.DUENUMBER;
                string StrOutPar = DbHelperSQL.RunProcedure("P_Storage_Check_Card_ADD", parameters, parameters[0]).ToString();
                return StrOutPar;


            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(Storage_Check_Card model)
        {
            try
            {
                OracleParameter[] parameters = {
					new OracleParameter("vID", OracleType.VarChar,50),
				    new OracleParameter("vCHECKNO", OracleType.VarChar,50),
					new OracleParameter("vSTORAGETYPE", OracleType.VarChar,50),
					new OracleParameter("vORDERID", OracleType.VarChar,50),
					new OracleParameter("vREMARK", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("vEXAMINER", OracleType.VarChar,50),
                    new OracleParameter("vSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("vSPAREPARTS", OracleType.Number,4),
					new OracleParameter("vPREMIUMS", OracleType.Number,4),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("vDueNumber", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.CHECKNO;
                parameters[2].Value = model.STORAGETYPE;
                parameters[3].Value = model.ORDERID;
                parameters[4].Value = model.REMARK;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.EXAMINER;
                parameters[8].Value = model.STORAGEDATE;
                parameters[9].Value = model.SPAREPARTS;
                parameters[10].Value = model.PREMIUMS;
                parameters[11].Value = model.IsSubmit;
                parameters[12].Value = model.DUENUMBER;
                parameters[13].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_Storage_Check_Card_Update", parameters, parameters[13]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(Storage_Check_Card model, string FileValue)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                string checkId = System.Guid.NewGuid().ToString();//主键ID
                try
                {
                    StringBuilder strSql = new StringBuilder(" begin ");
                    for (int i = 0; i < count; i++)
                    {
                        strSql.Append("insert into storage_detailed_card");
                        strSql.Append("(checkid,boxno,cardno,startno,endno,deservednumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + checkId + "',");
                        strSql.Append("" + split[i].Replace('|', ',') + "");
                        strSql.Append(");");
                        #region 插入卡明细
                        int isEC = 0;
                        //判断是否是二次利用入库
                        if (model.STORAGETYPE == "5DDA8F0A65AE4BDDA21A622EA6E1F257")
                        {
                            isEC = 1;
                        }
                        strSql.Append("P_CARDRECORD_Add_IN(" + split[i].Split('|')[2] + "," + split[i].Split('|')[3] + ",");
                        strSql.Append(split[i].Split('|')[6] + ",'" + checkId + "','" + model.ORDERID + "',to_date('" + model.STORAGEDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),");
                        strSql.Append(split[i].Split('|')[0] + "," + split[i].Split('|')[1] + "," + isEC + ");");
                        #endregion
                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
				    new OracleParameter("vCHECKNO", OracleType.VarChar,50),
					new OracleParameter("vSTORAGETYPE", OracleType.VarChar,50),
					new OracleParameter("vORDERID", OracleType.VarChar,50),
					new OracleParameter("vREMARK", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("vEXAMINER", OracleType.VarChar,50),
                	new OracleParameter("vSTORAGEDATE", OracleType.DateTime),
                	new OracleParameter("vSPAREPARTS", OracleType.Number,4),
					new OracleParameter("vPREMIUMS", OracleType.Number,4),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("vDueNumber", OracleType.Number,4),
                    new OracleParameter("VID", OracleType.VarChar)};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.CHECKNO;
                    parameters[2].Value = model.STORAGETYPE;
                    parameters[3].Value = model.ORDERID;
                    parameters[4].Value = model.REMARK;
                    parameters[5].Value = model.LASTUPDATEUSER;
                    parameters[6].Value = model.LASTUPDATEDATE;
                    parameters[7].Value = model.EXAMINER;
                    parameters[8].Value = model.STORAGEDATE;
                    parameters[9].Value = model.SPAREPARTS;
                    parameters[10].Value = model.PREMIUMS;
                    parameters[11].Value = model.IsSubmit;
                    parameters[12].Value = model.DUENUMBER;
                    parameters[13].Value = checkId;
                    StrOutPar = DbHelperSQL.RunProcedure("P_Storage_Check_Card_ADD", parameters, parameters[0]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(Storage_Check_Card model, string FileValue)
        {
            string StrOutPar = string.Empty;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                try
                {
                    StringBuilder strSql = new StringBuilder(" begin ");
                    strSql.Append(" delete from storage_detailed_card where checkid ='" + model.ID + "';");//删除已入库明细
                    strSql.Append(" delete from cardrecord where RKId='" + model.ID + "';");//删除卡详细流水记录表数据
                    strSql.Append(" delete from cardrecord_lack where RKID='" + model.ID + "';");//删除入库缺卡卡号流水记录
                    for (int i = 0; i < count; i++)
                    {
                        strSql.Append("insert into storage_detailed_card");
                        strSql.Append("(checkid,boxno,cardno,startno,endno,deservednumber,actualnumber,missingcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + model.ID + "',");
                        strSql.Append("" + split[i].Replace('|', ',') + "");
                        strSql.Append(");");
                        #region 插入卡明细
                        int isEC = 0;
                        //判断是否是二次利用入库
                        if (model.STORAGETYPE == "5DDA8F0A65AE4BDDA21A622EA6E1F257")
                        {
                            isEC = 1;
                        }
                        strSql.Append("P_CARDRECORD_Add_IN(" + split[i].Split('|')[2] + "," + split[i].Split('|')[3] + ",");
                        strSql.Append(split[i].Split('|')[6] + ",'" + model.ID + "','"+model.ORDERID+"',to_date('" + model.STORAGEDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),");
                        strSql.Append(split[i].Split('|')[0] + "," + split[i].Split('|')[1] + "," + isEC + ");");
                        #endregion
                    }
                    strSql.Append(" end; ");

                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        cmd.ExecuteNonQuery();
                    }

                    OracleParameter[] parameters = {
					new OracleParameter("vID", OracleType.VarChar,50),
				    new OracleParameter("vCHECKNO", OracleType.VarChar,50),
					new OracleParameter("vSTORAGETYPE", OracleType.VarChar,50),
					new OracleParameter("vORDERID", OracleType.VarChar,50),
					new OracleParameter("vREMARK", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("vEXAMINER", OracleType.VarChar,50),
                    new OracleParameter("vSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("vSPAREPARTS", OracleType.Number,4),
					new OracleParameter("vPREMIUMS", OracleType.Number,4),
                    new OracleParameter("vIsSubmit", OracleType.Number,4),
                    new OracleParameter("vDueNumber", OracleType.Number,4),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.CHECKNO;
                    parameters[2].Value = model.STORAGETYPE;
                    parameters[3].Value = model.ORDERID;
                    parameters[4].Value = model.REMARK;
                    parameters[5].Value = model.LASTUPDATEUSER;
                    parameters[6].Value = model.LASTUPDATEDATE;
                    parameters[7].Value = model.EXAMINER;
                    parameters[8].Value = model.STORAGEDATE;
                    parameters[9].Value = model.SPAREPARTS;
                    parameters[10].Value = model.PREMIUMS;
                    parameters[11].Value = model.IsSubmit;
                    parameters[12].Value = model.DUENUMBER;
                    parameters[13].Direction = ParameterDirection.Output;
                    StrOutPar = DbHelperSQL.RunProcedure("P_Storage_Check_Card_Update", parameters, parameters[13]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                        tx.Commit();
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_Storage_Check_Card_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
